package org.lq.mark.dao.impl;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.net.ntp.TimeStamp;

import lombok.Data;
import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lq.mark.dao.EmailDao;
import org.lq.mark.entity.Email;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.List;

/**
 * @author ming
 * @create 2020-10-13 18:53
 */
@Log4j
public class EmailDaoImpl implements EmailDao {

    /**
     * t添加
     *
     * @param email
     * @return
     */
    @Override
    public int save(Email email) {
        log.info("进入添加邮件");
        int num = 0;
        try {
            num = new QueryRunner(JDBCUtil.getDataSource()).update("insert into email_info(" +
                    "staff_id," +
                    "email_title," +
                    "email_content," +
                    "email_time," +
                    "email_man," +
                    "email_addr," +
                    "email_state)" +
                    "values(?,?,?,now(),?,?,?)",
                    email.getStaffId(),
                    email.getEmailTitle(),
                    email.getEmailContent(),
                    email.getEmailMan(),
                    email.getEmailAddress(),
                    email.getEmailState());
        } catch (SQLException e) {
           log.error("邮件添加出现异常"+e);
        }
        log.info("邮件添加结束");
        return  num;
    }

    /**
     * 修改
     *
     * @param email
     * @return
     */
    @Override
    public int update(Email email) {
        log.info("进入邮件修改");
        int num = 0;
        try {
            num = new QueryRunner(JDBCUtil.getDataSource()).update("update email_info set " +
                            "staff_id=?," +
                            "email_title=?," +
                            "email_content=?," +
                            "email_time=now()," +
                            "email_man=?," +
                            "email_addr=?," +
                            "email_state=? " +
                            "where email_id=?",
                            email.getStaffId(),
                            email.getEmailTitle(),
                            email.getEmailContent(),
                            email.getEmailMan(),
                            email.getEmailAddress(),
                            email.getEmailState(),
                            email.getEmailId()
            );
        } catch (SQLException e) {
            log.error("邮件修改出现异常"+e);
        }
        log.info("邮件修改结束");
        return  num;
    }

    /**
     * 删除
     *
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        int num =0;
        log.info("进入删除邮件");
        try {
            num = new QueryRunner(JDBCUtil.getDataSource()).update("delete from email_info where email_id=? ",id);
        } catch (SQLException e) {
           log.error("删除邮件出现异常"+e);
        }
        log.info("删除邮件结束");
        return num;
    }

    /**
     * 通过ID查询
     *
     * @param id
     * @return
     */
    @Override
    public Email getById(int id) {
        log.info("进入通过id查询邮件");
        Email e = null;
        try {
            e = new QueryRunner(JDBCUtil.getDataSource()).query("select *from v_email where emailId = ?",new BeanHandler<>(Email.class),id);
        } catch (SQLException ex) {
           log.error("通过查询出现异常"+ex);
        }
        log.info("借宿通过id查询邮件");
        return e;
    }

    /**
     * 总行数
     *
     * @return
     */
    @Override
    public int getCount() {
        log.info("进入查询总行数");
        int num = 0;
        try {
            num = new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_email",new ScalarHandler<Long>()).intValue();

        } catch (SQLException e) {
            log.error("查询总行数出现异常"+e);
        }
        log.info("结束查询总行数");
        return num;
    }

    /**
     * 分页查询
     *
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<Email> pageList(int startIndex, int pageSize) {
        log.info("进入分页查询");
        List<Email> list = null;
        try {
            list = new QueryRunner(JDBCUtil.getDataSource()).query("select *from v_email limit ?,?",
                    new BeanListHandler<>(Email.class),startIndex,pageSize);
        } catch (SQLException e) {
           log.error("分页查询出现异常"+e);
        }
        log.info("分页查询结束");
        return list;
    }

    /**
     * 根据条件查询总行数
     *
     * @param values
     * @return
     */
    @Override
    public int getCount(String... values) {
        log.info("根据主题查询邮件开始");
        int num = 0;
        try {
            num = new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_email where emailTitle like ?",
                    new ScalarHandler<Long>(),"%"+values[0]+"%").intValue();
        } catch (SQLException e) {
           log.error("根据邮箱主题查询出现异常"+e);
        }
        log.info("根据邮箱查询结束");
        return num;
    }

    /**
     * 根据条件分页查询
     *
     * @param startIndex
     * @param pageSize
     * @param value
     * @return
     */
    @Override
    public List<Email> pageByValues(int startIndex, int pageSize, String... value) {
        List<Email> list = null;
        log.info("进入通过主题分页查询");
        try {
            list = new QueryRunner(JDBCUtil.getDataSource()).query("select *from v_email where emailTitle like ? limit ?,?",
                    new BeanListHandler<>(Email.class),"%"+value[0]+"%",startIndex,pageSize);
        } catch (SQLException e) {
           log.error("通过主题分页出现异常"+e);
        }
        log.info("通过主体体分页查询结束");
        return list;
    }


}
